Released on: October 16, 2008, 3:08 pm

Press Release Author: Alba Spectrum LLC

Industry: Computers

Press Release Summary: Microsoft Dynamics GP Integration Manager module is pretty
simple, especially when your incoming data is prepared and structured in CSV or tab
delimited text file. The same result if data is ODBC compliant and you can easily
map it in the destination.

Press Release Body: Microsoft Dynamics GP Integration Manager module is pretty
simple, especially when your incoming data is prepared and structured in CSV or tab
delimited text file. The same result if data is ODBC compliant and you can easily
map it in the destination. However, very often you are in the situation when you
can not control the incoming text file and have to massage it in the query. In
order to achieve this goal, you need to deploy Advanced ODBC queries. Good example
is when you are importing customer invoices and some of them might be negative, and
in this case you need to treat them either as Sales Order Processing Returns or
Receivable Management Credit Memos. Let's review the steps and highlights:

1. Create ODBC DSN. Let's assume that you got export in comma separated values
(CSV) format. Open control panel, administrative tools, ODBC, select System tab,
Microsoft Text driver, uncheck Use Current Directory, select the directory where
text file is located, switch type to CSV and if you have column names in the first
row, then save and close the DSN. Sometimes on this step you are getting error
message, locking you from saving first row column names - if this happens to you -
just work with generic enumerated columns

2. Create Advanced ODBC query in IM. Here you should appeal to SQL select, group,
union and having clauses. Implementing the example from the header - in order to
filter out all the credit memos, please place the conditions on negative document
amount. Please, not that in the case of having invoice lines in the source -
negative document amount might be calculated in group by statement - check the
conditions on having sum(amount) less than zero

3. If SQL Query over the text file is too complex. In this case, consider creating
SQL Server DTS package, which will move text file into SQL staging table and process
it through one or several SQLL stored procedures to massage data to fit integration
requirements. When you are ready with the data table in SQL Server, base your
advanced ODBC query on SQL view

4. Beyond Integration Manager. If you need real time online integration, and if you
are on Great Plains version 9.0, 10.0 or newer, consider programming integration
routine in eConnect. eConnect allows you to create GP objects from your eCommerce
application


Web Site: http://www.albaspectrum.com

Contact Details: Andrew Karasev, Alba Spectrum LLC, help@albaspectrum.com,
http://www.albaspectrum.com, 1-866-528-0577 , Great Plains, VAR, Partner and
Reseller in Illinois, Georgia, South Carolina, California, Texas. Please visit our
info portal Pegas Planet: http://www.pegasplanet.com Local Service in Chicago,
Atlanta, San Diego, Los Angeles, Orange County, Houston. We also have Dexterity
Software Development Factory and SB1 Technical Support Call Center, serving USA
country side businesses and internationally: Canada, Brazil. Illinois practice:
Naperville, Aurora, Plainfield, Marseilles, Ottawa, DeKalb, Springfield, Oakbrook,
Glenn Ellyn, Wheaton, Bolingbrook, Romeoville, Lisle, Melrose Park, Brookfield. San
Diego and LA practice: Rancho Bernardo, Oceanside, Poway, Long Beach, Irvine,
Mission Viejo, San Marcos, Carlsbad, La Jolla, Escondido. Houston Practice: Dallas,
Galveston, Sugar Land, Pearland, Katy, Pasadena, Rosenberg, League City, Galena Park